﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using xycn.common;

namespace xycn.DbService
{
    /// <summary>
    /// Sql转换帮助类
    /// </summary>
    public class SqlHelper
    {
        /// <summary>
        /// 字典转换为Sql语句,T反射后 ToLower比对字段 过滤实体类不包含的字段 内容为空的自动过滤
        /// 转换的Sql语句字段大小写不变,根据数据库自行控制
        /// sql语句前缀不包含 and 关键字
        /// 字段key 通过竖线分隔 支持 逻辑关系 b|s|e|bl|el|l|ne|i  大于 小于 等于
        /// 字段key 通过竖线分隔 支持 and or 竖线 第三个值为 分组关键字,仅用于判重 不做业务逻辑
        /// 如 有4个key为 Name|l  Age|be|x  Sex|e|x  Class|e|y 得到Sql为  ( Name like  ?  ) or (Age >=  ?  AND Sex =  ?  ) or (Class =  ?  )
        /// 特殊处理当分组的其中一个名称为 inor 的时候 sql语句转换为  ( Name like  ?  ) and (Age >=  ?  or Sex =  ?  ) and (Class =  ?  )
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fieldValues">The field values.</param>
        /// <param name="values">The values.</param>
        /// <returns></returns>
        public static string GetSqlByDic<T>(Dictionary<string, object> fieldValues, out List<object> values) where T : new()
        {

            if (fieldValues == null)
            {
                values = new List<object>();
                return "";
            }
            var dicconv = new Dictionary<string, object>();

            T t = new T();

            var prolst = t.GetType().GetProperties();

            foreach (var item in fieldValues)
            {
                var fieldName = item.Key.Split('|')[0].ToLower().Split('&')[0];

                if (prolst.Count(p => p.Name.ToLower() == fieldName) > 0)
                {
                    dicconv.Add(item.Key, item.Value);
                }
            }
            return GetSqlByDic(dicconv, out values);
        }

        /// <summary>
        /// 字典转换为Sql语句,内容为空的自动过滤
        /// 转换的Sql语句字段大小写不变,根据数据库自行控制
        /// sql语句前缀不包含 and 关键字
        /// 字段key 通过竖线分隔 支持 逻辑关系 b|s|e|bl|el|l|ne|i 分别对应 大于|小于|等于|like|like|like|不等于|in
        /// 字段key 通过竖线分隔 支持 and or 竖线 第三个值为 分组关键字,仅用于判重 不做业务逻辑
        /// 如 有4个key为 Name|l  Age|be|x  Sex|e|x  Class|e|y 得到Sql为  ( Name like  ?  ) or (Age >=  ?  AND Sex =  ?  ) or (Class =  ?  )
        /// 特殊处理当分组的其中一个名称为 inor 的时候,表示内部的为or sql语句转换为  ( Name like  ?  ) and (Age >=  ?  or Sex =  ?  ) and (Class =  ?  )
        /// </summary>
        /// <param name="fieldValues">The field values.</param>
        /// <param name="values">The values.</param>
        /// <returns></returns>
        public static string GetSqlByDic(Dictionary<string, object> fieldValues, out List<object> values)
        {
            if (fieldValues == null)
            {
                values = new List<object>();
                return "";
            }

            values = new List<object>();

            StringBuilder sbwhere = new StringBuilder();

            bool inIsOr = false;

            var groupDic = DicGroupByWhere(fieldValues, ref inIsOr);

            #region 加一个逻辑处理 处理 a=1 and b=2 and (c=3 or d=4) 这种模式
            //Key 全小写
            var addwhere = new Dictionary<string, Dictionary<string, object>>();
            groupDic = DealTheSameWith(groupDic, ref addwhere);
            #endregion

            bool isfirstgroup = true;

            foreach (var dic in groupDic)
            {
                #region 每组条件之间连接关键字
                if (isfirstgroup)
                {
                    sbwhere.Append("( ");
                    isfirstgroup = false;
                }
                else
                {
                    //内部是否使用or
                    if (inIsOr)
                    {
                        //内部使用or则外部使用 and
                        sbwhere.Append(" and (");
                    }
                    else
                    {
                        sbwhere.Append(" or (");
                    }
                }
                #endregion

                #region 遍历 每一组条件 组装查询条件
                bool isfirstwhere = true;
                foreach (var item in dic.Value)
                {
                    if (item.Value == null || item.Value.ToString() == "")
                    {
                        continue;
                    }

                    #region 基础条件判断
                    // 0字段 1逻辑
                    var ary = GetLogic(item.Key);
                    if (isfirstwhere == false)
                    {
                        //内部是否使用or
                        if (inIsOr)
                        {
                            sbwhere.Append(" or ");
                        }
                        else
                        {
                            sbwhere.Append(" and ");
                        }
                    }
                    isfirstwhere = false;

                    #endregion

                    if (!addwhere.ContainsKey(ary[0].ToLower()))
                    {
                        #region 常规逻辑 a=2 

                        //sbwhere.Append($"{(ary[1] == "in" ? " FIND_IN_SET(" + ary[0] + ", ?) > 0" : ary[0] + " " + ary[1] + "  ? ")}");

                        if (ConfigHelper.GetConfigToString("ConnectionStrings:DataBaseType")?.ToLower() == "postgresql")
                        {
                            if (ary[1] == "in")
                            {
                                sbwhere.Append($" {ary[0]} in({item.Value.ToString()})");
                            }
                            else
                            {
                                sbwhere.Append($"{(ary[0] + " " + ary[1] + "  ? ")}");

                                if (ary[1] == "like")
                                {
                                    if (item.Key.ToLower().Contains("|bl|"))
                                    {
                                        values.Add(item.Value.ToString().Trim() + "%");
                                    }
                                    else if (item.Key.ToLower().Contains("|el|"))
                                    {
                                        values.Add("%" + item.Value.ToString().Trim());
                                    }
                                    else
                                    {
                                        values.Add("%" + item.Value.ToString().Trim() + "%");
                                    }
                                }
                                else
                                {
                                    values.Add(item.Value.ToString().Trim());
                                }
                            }
                        }
                        else
                        {
                            #region 以前默认逻辑 主要支持mysql

                            sbwhere.Append($"{(ary[1] == "in" ? " FIND_IN_SET(" + ary[0] + ", ?) > 0" : ary[0] + " " + ary[1] + "  ? ")}");

                            if (ary[1] == "like")
                            {
                                if (item.Key.ToLower().Contains("|bl|"))
                                {
                                    values.Add(item.Value.ToString().Trim() + "%");
                                }
                                else if (item.Key.ToLower().Contains("|el|"))
                                {
                                    values.Add("%" + item.Value.ToString().Trim());
                                }
                                else
                                {
                                    values.Add("%" + item.Value.ToString().Trim() + "%");
                                }
                            }
                            else
                            {
                                values.Add(item.Value.ToString().Trim());
                            }
                            #endregion
                        }

                        #endregion
                    }
                    else
                    {
                        #region 有附加条件的逻辑 先处理原字段 在附加逻辑 (a=1 or b=1)
                        sbwhere.Append("(");

                        if (ConfigHelper.GetConfigToString("ConnectionStrings:DataBaseType")?.ToLower() == "postgresql")
                        {
                            if (ary[1] == "in")
                            {
                                sbwhere.Append($" {ary[0]} in({item.Value.ToString()})");
                            }
                            else
                            {
                                sbwhere.Append($"{(ary[0] + " " + ary[1] + "  ? ")}");

                                if (ary[1] == "like")
                                {
                                    if (item.Key.ToLower().Contains("|bl|"))
                                    {
                                        values.Add(item.Value.ToString().Trim() + "%");
                                    }
                                    else if (item.Key.ToLower().Contains("|el|"))
                                    {
                                        values.Add("%" + item.Value.ToString().Trim());
                                    }
                                    else
                                    {
                                        values.Add("%" + item.Value.ToString().Trim() + "%");
                                    }
                                }
                                else
                                {
                                    values.Add(item.Value.ToString().Trim());
                                }
                            }
                        }
                        else
                        {
                            #region 历史逻辑 主要支持mysql
                            sbwhere.Append($"{(ary[1] == "in" ? " FIND_IN_SET(" + ary[0] + ", ?) > 0" : ary[0] + " " + ary[1] + "  ? ")}");

                            if (ary[1] == "like")
                            {
                                if (item.Key.ToLower().Contains("|bl|"))
                                {
                                    values.Add(item.Value.ToString().Trim() + "%");
                                }
                                else if (item.Key.ToLower().Contains("|el|"))
                                {
                                    values.Add("%" + item.Value.ToString().Trim());
                                }
                                else
                                {
                                    values.Add("%" + item.Value.ToString().Trim() + "%");
                                }
                            }
                            else
                            {
                                values.Add(item.Value.ToString().Trim());
                            }
                            #endregion
                        }

                        foreach (var addim in addwhere[ary[0].ToLower()])
                        {
                            // 0字段 1逻辑
                            var ary_att = GetLogic(addim.Key);

                            if (addim.Key.ToLower().Contains("|theorwith_"))
                            {
                                sbwhere.Append(" or ");
                            }
                            else if (addim.Key.ToLower().Contains("|theandwith_"))
                            {
                                sbwhere.Append(" and ");
                            }
                            else
                            {
                                continue;
                            }

                            if (ConfigHelper.GetConfigToString("ConnectionStrings:DataBaseType")?.ToLower() == "postgresql")
                            {
                                if (ary[1] == "in")
                                {
                                    sbwhere.Append($" {ary[0]} in({item.Value.ToString()})");
                                }
                                else
                                {
                                    sbwhere.Append($"{(ary_att[0] + " " + ary_att[1] + "  ? ")}");

                                    if (ary_att[1] == "like")
                                    {
                                        if (addim.Key.ToLower().Contains("|bl|"))
                                        {
                                            values.Add(addim.Value.ToString().Trim() + "%");
                                        }
                                        else if (addim.Key.ToLower().Contains("|el|"))
                                        {
                                            values.Add("%" + addim.Value.ToString().Trim());
                                        }
                                        else
                                        {
                                            values.Add("%" + addim.Value.ToString().Trim() + "%");
                                        }
                                    }
                                    else
                                    {
                                        values.Add(addim.Value.ToString().Trim());
                                    }
                                }
                            }
                            else
                            {
                                #region 历史逻辑主要支持mysql
                                sbwhere.Append($"{(ary_att[1] == "in" ? " FIND_IN_SET(" + ary_att[0] + ", ?) > 0" : ary_att[0] + " " + ary_att[1] + "  ? ")}");

                                if (ary_att[1] == "like")
                                {
                                    if (addim.Key.ToLower().Contains("|bl|"))
                                    {
                                        values.Add(addim.Value.ToString().Trim() + "%");
                                    }
                                    else if (addim.Key.ToLower().Contains("|el|"))
                                    {
                                        values.Add("%" + addim.Value.ToString().Trim());
                                    }
                                    else
                                    {
                                        values.Add("%" + addim.Value.ToString().Trim() + "%");
                                    }
                                }
                                else
                                {
                                    values.Add(addim.Value.ToString().Trim());
                                }
                                #endregion
                            }
                        }

                        sbwhere.Append(")");
                        #endregion
                    }

                }
                #endregion

                sbwhere.Append(" )");
            }

            if (values.Count() == 0)
            {
                sbwhere.Clear();
            }

            return sbwhere.ToString();
        }

        /// <summary>
        /// 根据字典扩展获取sql的逻辑关系 支持
        /// </summary>
        /// <param name="fieldName">Name of the field.</param>
        /// <returns></returns>
        public static string[] GetLogic(string fieldName)
        {
            if (fieldName.IndexOf('|') > 0)
            {
                var ary = fieldName.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
                if (ary.Length >= 2)
                {
                    //b|s|e|bl|el|l|ne|i  |l1|l2 扩展支持 多个 组
                    switch (ary[1].ToLower())
                    {
                        case "b":
                            ary[1] = ">";
                            break;
                        case "be":
                            ary[1] = ">=";
                            break;
                        case "s":
                            ary[1] = "<";
                            break;
                        case "se":
                            ary[1] = "<=";
                            break;
                        case "e":
                            ary[1] = "=";
                            break;
                        case "bl":
                            ary[1] = "like";
                            break;
                        case "el":
                            ary[1] = "like";
                            break;
                        case "l":
                            ary[1] = "like";
                            break;
                        case "ne":
                            ary[1] = "<>";
                            break;
                        case "i":
                            ary[1] = "in";
                            break;
                        default:
                            if (ary[1].ToLower().StartsWith("l"))
                            {
                                ary[1] = "like";
                            }
                            break;
                    }
                    return ary;
                }
                else
                {
                    ary[1] = "=";
                    return ary;
                }
            }
            return new string[] { fieldName, "=" };
        }

        /// <summary>
        /// 字典分组 支持Or查询条件 默认组 default
        /// Key 字段分组的第三个值
        /// Value为字段和值
        /// </summary>
        /// <param name="fieldValues">The field values.</param>
        /// <param name="inIsOr">分组内部是否使用Or逻辑</param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, object>> DicGroupByWhere(Dictionary<string, object> fieldValues, ref bool inIsOr)
        {
            inIsOr = false;

            var retdic = new Dictionary<string, Dictionary<string, object>>();

            retdic.Add("default", new Dictionary<string, object>());

            foreach (var item in fieldValues)
            {
                var ary = item.Key.Split('|');

                if (ary.Length < 3)
                {
                    retdic["default"].Add(item.Key, item.Value);
                }
                else
                {
                    var groupKey = ary[2].ToLower();

                    if (groupKey == "inor" && inIsOr == false)
                    {
                        inIsOr = true;
                    }
                    if (!retdic.ContainsKey(groupKey))
                    {
                        retdic.Add(groupKey, new Dictionary<string, object>());
                    }
                    retdic[groupKey].Add(item.Key, item.Value);
                }
            }

            if (retdic["default"].Count() == 0)
            {
                retdic.Remove("default");
            }

            return retdic;
        }

        /// <summary>
        /// 处理 a=1 and b=2 and (c=3 or d=4) 这种模式 对 字典的第三个条件进行判断处理
        /// </summary>
        /// <param name="olddic">原始条件</param>
        /// <param name="addWhere">处理后的 累加条件</param>
        /// <returns></returns>
        public static Dictionary<string, Dictionary<string, object>> DealTheSameWith(Dictionary<string, Dictionary<string, object>> olddic, ref Dictionary<string, Dictionary<string, object>> addWhere)
        {
            addWhere = new Dictionary<string, Dictionary<string, object>>();

            var orwithdata = new Dictionary<string, Dictionary<string, object>>();
            foreach (var item in olddic)
            {
                if (item.Key.ToLower().StartsWith("theorwith_") || item.Key.ToLower().StartsWith("theandwith_"))
                {
                    orwithdata.Add(item.Key, item.Value);
                }
            }
            if (orwithdata.Count() > 0)
            {
                foreach (var delkey in orwithdata)
                {
                    olddic.Remove(delkey.Key);
                }
                foreach (var item in orwithdata)
                {
                    var samewitjfield = item.Key.Substring(item.Key.IndexOf("_") + 1, item.Key.Length - item.Key.IndexOf("_") - 1);
                    addWhere.Add(samewitjfield, new Dictionary<string, object>());
                    foreach (var sub in item.Value)
                    {
                        addWhere[samewitjfield].Add(sub.Key, sub.Value);
                    }
                }
            }

            return olddic;
        }

        /// <summary>
        /// 获取操作Demo演示数据
        /// </summary>
        /// <returns></returns>
        public static string GetDemoDescribe()
        {
            //演示SqlHelper使用方式,说明dic对应的sqlwhere输出结果
            //方式1 简单字典组合
            var dic = new Dictionary<string, object>();
            dic.Add("a", 1);
            dic.Add("b", 2);
            dic.Add("c", 3);

            //输出sql结果为
            var sqlwhere = "( a =  ?  and b =  ?  and c =  ?  )";

            //方式2 大于 小于 like方式
            dic = new Dictionary<string, object>();
            dic.Add("a|b", 1);
            dic.Add("b|be", 2);
            dic.Add("c|l", 3);
            dic.Add("d|ne", 4);
            dic.Add("e|i", "5,6");
            sqlwhere = "( a >  ?  and b >=  ?  and c like  ?  and d <>  ?  and  FIND_IN_SET(e, ?) > 0 )";

            //方式3 or 方式使用 key第三个关键字分组 其中g1 g2 g3仅用于分组,无数据库业务逻辑
            dic = new Dictionary<string, object>();
            dic.Add("a|b|g1", 1);
            dic.Add("b|be|g1", 2);
            dic.Add("c|l|g2", 3);
            dic.Add("d|ne|g2", 4);
            dic.Add("e|i|g3", "5,6");
            sqlwhere = "( a >  ?  and b >=  ?  ) or (c like  ?  and d <>  ?  ) or ( FIND_IN_SET(e, ?) > 0 )";

            //方式4 在方式3的基础上 如果分组名 其中一个为 inor 则sql预计括号内为or
            dic = new Dictionary<string, object>();
            dic.Add("a|b|g1", 1);
            dic.Add("b|be|g1", 2);
            dic.Add("c|l|g2", 3);
            dic.Add("d|ne|g2", 4);
            dic.Add("e|i|inor", "5,6");
            sqlwhere = "( a >  ?  or b >=  ?  ) and (c like  ?  or d <>  ?  ) and ( FIND_IN_SET(e, ?) > 0 )";

            //方式5 基于方式3和方式4简化优化 theorwith_
            dic = new Dictionary<string, object>();
            dic.Add("a|b", 1);
            dic.Add("b|be", 2);
            dic.Add("c|l", 3);
            dic.Add("d|ne|theorwith_a", 4);
            dic.Add("e|ne|theorwith_a", 5);
            sqlwhere = "( (a >  ?  or d <>  ?  or e <>  ? ) and b >=  ?  and c like  ?  )";

            //方式6 类似方式5 theandwith_ 此方式意义不大 适用于or and组合
            dic = new Dictionary<string, object>();
            dic.Add("a|b", 1);
            dic.Add("b|be", 2);
            dic.Add("c|l", 3);
            dic.Add("d|ne|theandwith_a", 4);
            dic.Add("e|ne|theandwith_a", 5);
            sqlwhere = "( (a >  ?  and d <>  ?  and e <>  ? ) and b >=  ?  and c like  ?  )";

            //方式7 theandwith_  theorwith_  or and组合
            dic = new Dictionary<string, object>();
            dic.Add("a|b", 1);
            dic.Add("b|be|theorwith_a", 2);
            dic.Add("c|l", 3);
            dic.Add("d|ne|theorwith_a", 4);
            dic.Add("e|ne|theandwith_c", 5);
            sqlwhere = "( (a >  ?  or b >=  ?  or d <>  ? ) and (c like  ?  and e <>  ? ) )";

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("//演示SqlHelper使用方式,说明dic对应的sqlwhere输出结果");
            sb.AppendLine("//方式1 简单字典组合");
            sb.AppendLine("var dic = new Dictionary<string, object>();");
            sb.AppendLine("dic.Add(\"a\", 1);");
            sb.AppendLine("dic.Add(\"b\", 2);");
            sb.AppendLine("dic.Add(\"c\", 3);");
            sb.AppendLine("");
            sb.AppendLine("//输出sql结果为");
            sb.AppendLine("var sqlwhere = \"( a =  ?  and b =  ?  and c =  ?  )\";");
            sb.AppendLine("");
            sb.AppendLine("//方式2 大于 小于 like方式");
            sb.AppendLine("dic = new Dictionary<string, object>();");
            sb.AppendLine("dic.Add(\"a|b\", 1);");
            sb.AppendLine("dic.Add(\"b|be\", 2);");
            sb.AppendLine("dic.Add(\"c|l\", 3);");
            sb.AppendLine("dic.Add(\"d|ne\", 4);");
            sb.AppendLine("dic.Add(\"e|i\", \"5,6\");");
            sb.AppendLine("sqlwhere = \"( a >  ?  and b >=  ?  and c like  ?  and d <>  ?  and  FIND_IN_SET(e, ?) > 0 )\";");
            sb.AppendLine("");
            sb.AppendLine("//方式3 or 方式使用 key第三个关键字分组 其中g1 g2 g3仅用于分组,无数据库业务逻辑");
            sb.AppendLine("dic = new Dictionary<string, object>();");
            sb.AppendLine("dic.Add(\"a|b|g1\", 1);");
            sb.AppendLine("dic.Add(\"b|be|g1\", 2);");
            sb.AppendLine("dic.Add(\"c|l|g2\", 3);");
            sb.AppendLine("dic.Add(\"d|ne|g2\", 4);");
            sb.AppendLine("dic.Add(\"e|i|g3\", \"5,6\");");
            sb.AppendLine("sqlwhere = \"( a >  ?  and b >=  ?  ) or (c like  ?  and d <>  ?  ) or ( FIND_IN_SET(e, ?) > 0 )\";");
            sb.AppendLine("");
            sb.AppendLine("//方式4 在方式3的基础上 如果分组名 其中一个为 inor 则sql预计括号内为or");
            sb.AppendLine("dic = new Dictionary<string, object>();");
            sb.AppendLine("dic.Add(\"a|b|g1\", 1);");
            sb.AppendLine("dic.Add(\"b|be|g1\", 2);");
            sb.AppendLine("dic.Add(\"c|l|g2\", 3);");
            sb.AppendLine("dic.Add(\"d|ne|g2\", 4);");
            sb.AppendLine("dic.Add(\"e|i|inor\", \"5,6\");");
            sb.AppendLine("sqlwhere = \"( a >  ?  or b >=  ?  ) and (c like  ?  or d <>  ?  ) and ( FIND_IN_SET(e, ?) > 0 )\";");
            sb.AppendLine("");
            sb.AppendLine("//方式5 基于方式3和方式4简化优化 theorwith_");
            sb.AppendLine("dic = new Dictionary<string, object>();");
            sb.AppendLine("dic.Add(\"a|b\", 1);");
            sb.AppendLine("dic.Add(\"b|be\", 2);");
            sb.AppendLine("dic.Add(\"c|l\", 3);");
            sb.AppendLine("dic.Add(\"d|ne|theorwith_a\", 4);");
            sb.AppendLine("dic.Add(\"e|ne|theorwith_a\", 5);");
            sb.AppendLine("sqlwhere = \"( (a >  ?  or d <>  ?  or e <>  ? ) and b >=  ?  and c like  ?  )\";");
            sb.AppendLine("");
            sb.AppendLine("//方式6 类似方式5 theandwith_ 此方式意义不大 适用于or and组合");
            sb.AppendLine("dic = new Dictionary<string, object>();");
            sb.AppendLine("dic.Add(\"a|b\", 1);");
            sb.AppendLine("dic.Add(\"b|be\", 2);");
            sb.AppendLine("dic.Add(\"c|l\", 3);");
            sb.AppendLine("dic.Add(\"d|ne|theandwith_a\", 4);");
            sb.AppendLine("dic.Add(\"e|ne|theandwith_a\", 5);");
            sb.AppendLine("sqlwhere = \"( (a >  ?  and d <>  ?  and e <>  ? ) and b >=  ?  and c like  ?  )\";");
            sb.AppendLine("");
            sb.AppendLine("//方式7 theandwith_  theorwith_  or and组合");
            sb.AppendLine("dic = new Dictionary<string, object>();");
            sb.AppendLine("dic.Add(\"a|b\", 1);");
            sb.AppendLine("dic.Add(\"b|be|theorwith_a\", 2);");
            sb.AppendLine("dic.Add(\"c|l\", 3);");
            sb.AppendLine("dic.Add(\"d|ne|theorwith_a\", 4);");
            sb.AppendLine("dic.Add(\"e|ne|theandwith_c\", 5);");
            sb.AppendLine("sqlwhere = \"( (a >  ?  or b >=  ?  or d <>  ? ) and (c like  ?  and e <>  ? ) )\";");

            return sb.ToString();
        }
    }
}
